<?php
use OC\Files\Cache\Cache;

JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_puser/tables');
JTable::addIncludePath(JPATH_ADMINISTRATOR.'/components/com_jobs/tables');
JLoader::register("QueueHelper", JPATH_SITE.'/components/com_jobmail/helpers/queue.php');
JLoader::register('PCompanyModelSubAccounts', OC::$SERVERROOT."/apps/company/lib/subaccounts.php");
/**
 * Resumes controller
 */
class PResumesModelResume extends JModelLegacy
{
   public function add() {
      return true; 
   }

   public function trash($trash=true) {
      $resumeids = json_decode(JRequest::getString("ids", "[]"));
      if(empty($resumeids))
         return true; 


      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();

      JArrayHelper::toInteger($resumeids);
      $resumeids = self::checkPermissions($resumeids, OCP\PERMISSION_DELETE);
      if(empty($resumeids)) {
         $this->setError("您无权访问该资源"); 
         return false;
      }
      
      if($trash) {
         // put the resume to be trashed, we will not do real remove until later 
         $query = "UPDATE *PREFIX*hr_jobseeker set is_trashed=1, date_trashed=UTC_TIMESTAMP() WHERE id IN (" . implode(",", $resumeids) . ") AND company_id = " . $user->id;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("删除失败!");
            return false;
         }
      } else {
         $userQuota = PCompanyHelper::getUserQuota();
         if(($userQuota['num_jobseekers'] + count($resumeids)) >= $userQuota['quota']['resume_max'] && PCompanyHelper::checkQuotaEnable()) {
            $this->setError('超过简历数上限，请重新选择');
            return false;
         }
         
         $query = "UPDATE *PREFIX*hr_jobseeker set is_trashed=0, date_trashed=UTC_TIMESTAMP() WHERE id IN (" . implode(",", $resumeids) . ") AND company_id = " . $user->id;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("恢复失败!");
            return false;
         }
      }

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker'));
      foreach($resumeids as $resumeid) {
         CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker-'.$resumeid));
      }
      
      // get related folderids
      $query = "SELECT DISTINCT folder_id FROM *PREFIX*hr_resume_folder_files WHERE jobseeker_id IN (" . implode(",", $resumeids) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $relatedFolderids = $db->loadAssocList('', 'folder_id');
      
      // update total number of related folder
      if (!empty($relatedFolderids)) {
         $query = "UPDATE *PREFIX*hr_resume_folder AS rf 
                   SET rf.num=(SELECT COUNT(*) FROM  *PREFIX*hr_resume_folder_files AS f 
                               LEFT JOIN *PREFIX*hr_jobseeker AS j ON j.id=f.jobseeker_id 
                               LEFT JOIN #__jobs_job AS jj ON jj.id=j.job_id
                               WHERE f.folder_id=rf.folder_id AND j.is_trashed=0 AND jj.is_trashed=0)
                   WHERE rf.folder_id IN (" . implode(",", $relatedFolderids) . ")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
         
         CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_resume_folder', '*PREFIX*hr_resume_folder_files'));
      }
      
      // update resume number of company
      $query = "UPDATE #__jobs_employer AS je SET je.num_jobseekers=(SELECT COUNT(*) FROM  *PREFIX*hr_jobseeker AS j
                                                                     LEFT JOIN #__jobs_job AS jj ON jj.id=j.job_id
                                                                     WHERE j.is_trashed=0 AND jj.is_trashed=0 AND j.company_id=je.user_id)
                WHERE je.user_id=" . $user->id;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      
      CacheHelper::triggerRemove($user->id, array('#__jobs_employer'));

      // update total number of applicants 
      $query = "UPDATE #__jobs_job AS a 
               SET num_applied = (SELECT COUNT(*) FROM  *PREFIX*hr_jobseeker WHERE is_trashed=0 AND job_id=a.id)
               WHERE a.id IN (
                  SELECT job_id 
                  FROM *PREFIX*hr_jobseeker 
                  WHERE id IN (" . implode(",", $resumeids) . ") AND company_id = " . $user->id . ") 
               AND @jobids:= (
                  SELECT GROUP_CONCAT(job_id) 
                  FROM *PREFIX*hr_jobseeker 
                  WHERE id IN (" . implode(",", $resumeids) . ") AND company_id = " . $user->id . ")"; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      $query = "SELECT @jobids"; 
      $db->setQuery($db->useLastConnection($query));
      $jobids = $db->loadResult(); 
      if($jobids) {
         $jobids = explode(",", $jobids); 
         $jobids = array_filter($jobids); 
         foreach($jobids as $jobid) {
            // Event Driven Cache/Object Caching - clear any cache related to affected tables 
            CacheHelper::triggerRemove($user->id, array('#__jobs_job-'.intval($jobid)));
         }
      }

      return true; 
   }

   public function permdelete() {
      $resumeids = json_decode(JRequest::getString("ids", "[]"));
      if(empty($resumeids))
         return true;

      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();

      // put the resume to be trashed, we will not do real remove until later 
      JArrayHelper::toInteger($resumeids);
      $resumeids = self::checkPermissions($resumeids, OCP\PERMISSION_DELETE);
      if(empty($resumeids)) {
         $this->setError("您无权访问该资源");
         return false;
      }

      // for permanent delete, we need to do: 
      // 1. move the resume folder out from current employer directory 
      // 2. update its company_id to be 0 

      $query = "SELECT j.id, u.created".
               " FROM *PREFIX*hr_jobseeker AS j" .
               " LEFT JOIN *PREFIX*hr_jobseeker_uploaded AS u ON u.jobseeker_id=j.id" .
               " WHERE j.id IN (" . implode(",", $resumeids) . ") AND j.company_id=" . intval($user->id); 
      
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         $this->setError("删除失败!");
         return false;
      }

      $resumes = $db->loadAssocList('id');
      $resumelist = JArrayHelper::getColumn($resumes, 'id');
      if(empty($resumelist))
         return true;
      // there could be duplicate data, let's see how we can avoid this 
      $query = "SELECT j.id as jid, s.id as sid" . 
               " FROM *PREFIX*hr_jobseeker AS j " . 
               " LEFT JOIN *PREFIX*hr_jobseeker AS s ON s.email= j.email AND s.job_id=j.job_id" . 
               " WHERE j.id IN (" . implode(",", $resumelist) . ") AND s.company_id=0"; 

      $db->setQuery(PUtil::replaceOCPrefix($query));
      if(!$db->execute()) {
         $this->setError("删除失败!");
         return false;
      }

      $resumeduplicate = $db->loadAssocList(); 
      // get a list which was not never removed before 
      $resumeids_removed = JArrayHelper::getColumn($resumeduplicate, "jid");
      $resumelist2 = array_diff($resumelist, $resumeids_removed);

      if (!empty($resumelist2)) {
         $query = "UPDATE *PREFIX*hr_jobseeker set company_id=0, apply_company_id=" . $user->id . " WHERE id IN (" . implode(",", $resumelist2) . ") AND company_id = " . $user->id;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("删除失败!");
            return false;
         }
      }
      
      // delete from other folder
      $query = "DELETE FROM *PREFIX*hr_resume_folder_files WHERE jobseeker_id IN (" . implode(",", $resumelist) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      /* remove any shared data */
      $query = "DELETE FROM *PREFIX*share WHERE item_type='resumes' AND item_source IN (" . implode(",", $resumelist) . ")"; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute(); 

      //remove jobseeker_comments
      $query="DELETE FROM *PREFIX*hr_jobseeker_comments WHERE jobseeker_id IN (" . implode(",", $resumelist) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker', '*PREFIX*share'));
      foreach($resumelist as $resumeid) {
         CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker-'.$resumeid));
      }

      // Get all the parent object id related to affected user for cache clean use
      /*$query = "SELECT DISTINCT a.parent
         FROM *PREFIX*calendar_interview_events a
         LEFT JOIN *PREFIX*calendar_objects b ON b.id = a.objectid
         WHERE a.jobseekerid IN (".implode(",", $resumelist).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $parents = $db->loadAssocList('','parent');

      // Delete calendar and object related tables
      $query = "DELETE co, cfa, cie, cod, cof, cir
         FROM *PREFIX*calendar_objects co
         LEFT JOIN *PREFIX*calendar_feedback_answer cfa ON cfa.objectid = co.id
         LEFT JOIN *PREFIX*calendar_interview_events cie ON cie.objectid = co.id
         LEFT JOIN *PREFIX*calendar_objects_details cod ON cod.objectid = co.id
         LEFT JOIN *PREFIX*calendar_offer cof ON cof.objectid = co.id
         LEFT JOIN *PREFIX*calendar_interview_result cir ON cir.last_offer_objectid = co.id
         WHERE cie.jobseekerid IN (".implode(",", $resumelist).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      // Clean calendar app list view and calendar view cache
      CacheHelper::triggerRemove($user->id, array('*PREFIX*calendar_objects', '*PREFIX*calendar_interview_events', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result', '*PREFIX*calendar_offer', '*PREFIX*calendar_feedback_answer'));
      // Clean each object related preview or edit view data cache
      foreach ($parents as $k => $objectid) {
         CacheHelper::triggerRemove($user->id, array('*PREFIX*calendar_objects-'.$objectid, '*PREFIX*calendar_interview_events-'.$objectid, '*PREFIX*calendar_objects_details-'.$objectid, '*PREFIX*calendar_interview_result-'.$objectid, '*PREFIX*calendar_offer-'.$objectid, '*PREFIX*calendar_feedback_answer-'.$objectid));
      }*/

      if(empty($resumeids_removed))
         return true; 

      // the resumeids_removed is duplication of previous resumes, we need to remove them from database too 
      $query = "DELETE FROM *PREFIX*hr_jobseeker WHERE id IN (" . implode(",", $resumeids_removed) . ") AND company_id = " . $user->id; 
      $db->setQuery(PUtil::replaceOCPrefix($query)); 
      if(!$db->execute()) {
         $this->setError("删除失败!");
         return false;
      }
 
      // remove this resume from hr_jobseeker_education/jobseeker_workexp
      
      $query = "DELETE FROM *PREFIX*hr_jobseeker_education WHERE jobseeker_id IN (" . implode(",", $resumeids_removed) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute(); 

      $query = "DELETE FROM *PREFIX*hr_jobseeker_workexp WHERE jobseeker_id IN (" . implode(",", $resumeids_removed) .")"; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute(); 

      // Get all the parent object id related to affected user for cache clean use
      /*$query = "SELECT DISTINCT a.parent
         FROM *PREFIX*calendar_interview_events a
         LEFT JOIN *PREFIX*calendar_objects b ON b.id = a.objectid
         WHERE a.jobseekerid IN (".implode(",", $resumeids_removed).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $parents2 = $db->loadAssocList('','parent');

      // Delete calendar and object related tables
      $query = "DELETE co, cfa, cie, cod, cof, cir
         FROM *PREFIX*calendar_objects co
         LEFT JOIN *PREFIX*calendar_feedback_answer cfa ON cfa.objectid = co.id
         LEFT JOIN *PREFIX*calendar_interview_events cie ON cie.objectid = co.id
         LEFT JOIN *PREFIX*calendar_objects_details cod ON cod.objectid = co.id
         LEFT JOIN *PREFIX*calendar_offer cof ON cof.objectid = co.id
         LEFT JOIN *PREFIX*calendar_interview_result cir ON cir.last_offer_objectid = co.id
         WHERE cie.jobseekerid IN (".implode(",", $resumeids_removed).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();*/

      //FIXME: any other tables

      // Clean calendar app list view and calendar view cache
      /*CacheHelper::triggerRemove($user->id, array('*PREFIX*calendar_objects', '*PREFIX*calendar_interview_events', '*PREFIX*calendar_objects_details', '*PREFIX*calendar_interview_result', '*PREFIX*calendar_offer', '*PREFIX*calendar_feedback_answer'));
      // Clean each object related preview or edit view data cache
      foreach ($parents2 as $k => $objectid) {
         CacheHelper::triggerRemove($user->id, array('*PREFIX*calendar_objects-'.$objectid, '*PREFIX*calendar_interview_events-'.$objectid, '*PREFIX*calendar_objects_details-'.$objectid, '*PREFIX*calendar_interview_result-'.$objectid, '*PREFIX*calendar_offer-'.$objectid, '*PREFIX*calendar_feedback_answer-'.$objectid));
      }*/

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      foreach($resumeids_removed as $resumeid) {
         CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker-'.$resumeid));
      }
      return true; 
   }

   public function update() {
      return true; 

   }


   public function save($save=true) {
      $resumeids = json_decode(JRequest::getString("ids", ""));
      if(empty($resumeids))
         return true; 

      JArrayHelper::toInteger($resumeids);
      /* no limit for save
      $resumeids = self::checkPermissions($resumeids, OCP\PERMISSION_DELETE);
      */
      if(empty($resumeids)) {
         $this->setError("您无权访问该资源");
         return false;
      }

      $db = PFactory::getDbo();
      
      $values = array();
      $folder_id = PResumesHelper::getSavedFolderID();
      if(!$folder_id) {
         $this->setError("操作失败！");
         return false;
      }
      
      if($save) {
         foreach ($resumeids as $resumeid) {
            $values[] = "(" . $folder_id . ", " . $resumeid . ")";
         }
         $query = "INSERT IGNORE INTO *PREFIX*hr_resume_folder_files (folder_id, jobseeker_id) VALUES " . implode(",", $values);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("保存失败!");
            return false;
         }
      } else {
         $query = "DELETE FROM *PREFIX*hr_resume_folder_files WHERE folder_id=" . $folder_id . " AND jobseeker_id IN (" . implode(",", $resumeids) . ")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if(!$db->execute()) {
            $this->setError("取消保存失败!");
            return false;
         }
      }
      
      // update total number of saved folder
      $query = "UPDATE *PREFIX*hr_resume_folder AS rf SET rf.num=(SELECT COUNT(*) FROM  *PREFIX*hr_resume_folder_files WHERE folder_id=" . $folder_id . ")
                WHERE rf.folder_id=" . $folder_id;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_jobseeker', '*PREFIX*hr_resume_folder_files'));
      foreach($resumeids as $resumeid) {
         CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_jobseeker-'.$resumeid));
      }
      return true; 
   }

   public function getResumeDetail($resumeid=0) {
      // only to get resume parsed information 
      $query = "SELECT DISTINCT a.*, wp.*, je.*, jd.degree_level " . 
               " FROM *PREFIX*hr_jobseeker a" .
               " LEFT JOIN *PREFIX*hr_jobseeker_workexp wp ON wp.jobseeker_id = a.id" .
               " LEFT JOIN *PREFIX*hr_jobseeker_education je ON je.jobseeker_id = a.id" .
               " LEFT JOIN *PREFIX*hr_degree_level jd ON jd.id = je.id_degree_level" .
               " WHERE a.id = " . intval($resumeid) .
               " ORDER BY wp.job_year_start DESC, je.school_year_start DESC";

      /* try to load from cache */
      $cache = CacheHelper::getCache('app_resumes');
      // get cached data now 
      $data = $cache->call(array($this, '_getResumeDetail'), $query);

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user 
      CacheHelper::setTriggers(PFactory::getMasterUser()->get('id'), $cache, array('*PREFIX*hr_jobseeker-'.$resumeid));
   
      return $data; 
   }

   public function _getResumeDetail($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }
  
   // get information with job detail and upload information if it's present 
   public function getResumeJobDetail($resumeid=0 , $companyid=0) {
      $db = PFactory::getDbo();
      $user = PFactory::getMasterUser();
      if(!$companyid)
         $companyid = $user->id;

      $query = "SELECT DISTINCT hj.*, r.hashkey, r.latest_version, b.id AS attid,b.upload_attachment as attachment,b.creator,jj.id as job_id,jj.system, jj.job_title " . 
               " FROM *PREFIX*hr_jobseeker AS hj
                 LEFT JOIN *PREFIX*resumes AS r ON r.id = hj.resume_id
                 LEFT JOIN #__jobs_job AS jj ON jj.id = hj.job_id
                 LEFT JOIN *PREFIX*hr_jobseeker_uploaded AS b ON b.jobseeker_id = hj.id 
                 LEFT JOIN #__jobs_employer AS je ON je.user_id = hj.company_id
                 WHERE je.user_id=" . intval($companyid) . " AND hj.id=" . intval($resumeid);

      /* try to load from cache */
      $cache = CacheHelper::getCache('app_resumes');
      // get cached data now 
      $data = $cache->call(array($this, '_getResumeJobDetail'), $query);

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user 
      if(CacheHelper::needTriggers($cache) && $data) {
         CacheHelper::setTriggers($user->id, $cache, array('*PREFIX*hr_jobseeker-'.$resumeid, '*PREFIX*hr_jobseeker__uploadattachment-'.$resumeid, '*PREFIX*resumes-'.$data->resume_id));
      }

      return $data; 
   }

   public function _getResumeJobDetail($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   public function getResumeAsFiles($companyid, $resumeids) {
      JArrayHelper::toInteger($resumeids);
      $db = PFactory::getDbo();
      $query = "SELECT j.*, rs.hashkey, rs.latest_version, s.id as jobid, s.job_title as jobtitle, s.num_applied, s.online_interview_requirements, 
                       s.auto_invite, s.system as jobsystem, e.comp_name, hjs.title AS status_title, jj.sheetid, js.totalscore AS jobtest_totalscore,ju.creator, ". 
               " (SELECT COUNT(*) FROM *PREFIX*hr_jobseeker AS tj WHERE tj.is_trashed=0 AND tj.job_id=j.job_id AND tj.online_interview_testscore>j.online_interview_testscore) AS rank, " . 
               " IF(s.auto_invite OR !hjt.auto_send, hjt.id, 0) AS token_id, hjt.create_date AS invite_date, " .
               " IF(rff.folder_id IS NULL, 0, 1) as is_saved" .
               " FROM *PREFIX*hr_jobseeker AS j " . 
               " LEFT JOIN *PREFIX*resumes AS rs ON rs.id=j.resume_id " . 
               " LEFT JOIN *PREFIX*hr_jobseeker_uploaded AS ju ON ju.jobseeker_id=j.id " .
               " LEFT JOIN #__jobs_job AS s ON s.id=j.job_id " .
               " LEFT JOIN #__jobs_employer AS e ON e.user_id = s.employer_id AND e.user_id=j.company_id " . 
               " LEFT JOIN *PREFIX*hr_jobseeker_status AS hjs ON hjs.id=j.status_id " .
               " LEFT JOIN #__jobtest_jobsheet AS jj ON jj.jobid=j.job_id " .
               " LEFT JOIN #__jobtest_sheet AS js ON js.id=jj.sheetid " .
               " LEFT JOIN *PREFIX*hr_jobseeker_token AS hjt ON hjt.jobseeker_id=j.id AND hjt.disable=0 " .
               " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1) 
                 LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON (rff.folder_id=rf.folder_id AND rff.jobseeker_id=j.id) " .
               " WHERE j.id IN (" . implode(",", $resumeids) . ") AND e.user_id=" . intval($companyid); 

      /* try to load from cache */
      $cache = CacheHelper::getCache('app_resumes');
      // get cached data now 
      $data = $cache->call(array($this, '_getResumeAsFiles'), $query);

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user - Event Driven Cache/Object Caching
      if(CacheHelper::needTriggers($cache) && $data) {
         foreach($data as $resume) { 
            $tables = array('*PREFIX*hr_jobseeker-'.$resume->id,
                            '*PREFIX*resumes-'.$resume->resume_id,
                            '*PREFIX*hr_jobseeker_uploaded-'.$resume->id,
                            '*PREFIX*hr_jobseeker_status-'.$resume->id, 
                            '*PREFIX*hr_jobseeker_token-'.$resume->id, 
                            '#__jobtest_sheet-'.$resume->sheetid,
                            '#__jobs_job-'.$resume->job_id,
                            '*PREFIX*hr_company_interview-'.$resume->job_id);
            CacheHelper::setTriggers(intval($companyid), $cache, $tables);
         }
      }

      return $data;

   }

   public function _getResumeAsFiles($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList(); 
   }

   public function setResumeNewStatus($resumeid=0, $new=false) {
      //shura
      $db = PFactory::getDbo();

      $user = PFactory::getMasterUser(); 

      $query = "UPDATE *PREFIX*hr_jobseeker SET is_new=" . ($new ? 1 : 0) . " WHERE id=" . intval($resumeid) . " AND company_id=" . $user->id . " and source=@resumesource := source;"; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();  

      $db->setQuery($db->useLastConnection('SELECT @resumesource'));
      $resumesource = $db->loadResult();

      try {
         if(strpos($resumesource,'lagou')!== FALSE){
            $queue = new QueueHelper();
            $data = array();
            $data['resumeid'] = $resumeid;
            $data['status_id'] = 0;
            $tubename = 'localremoteupdateTube';
            $config = PFactory::getConfig();
            // need to check if global queue enabled. If not, everything should be in localhost beanstalkd 
            if($config->get('globalqueue')) {
               $task = array();
               $task['tube'] = $tubename;
               $task['data'] = $data;
               $task['companyid'] = PFactory::getMasterUser()->id;
               $data = $task;
               $tubename = 'globaltaskTube';
            }

            $queue->putTask($data, $tubename, 0);
         }
      } catch(Exception $e) {}

      // Event Driven Cache/Object Caching - clear any cache related to affected tables 
      CacheHelper::triggerRemove($user->id, array('*PREFIX*hr_jobseeker'));
   }
   
   public function getNextResume($resumeid=0) {
      if(!$resumeid)
         return array(null, null);

      $app = PFactory::getApplication();
      $token = JRequest::getString('t', '');
      $searchtokens = $app->getUserState('searchtokens', null);
      $searchtoken = (!empty($token) && !empty($searchtokens[$token])) ? $searchtokens[$token] : null;
      if(empty($searchtoken))
         return array(null, null);
      
      $dir = JArrayHelper::getValue($searchtoken, 'dir', null);
      $joinSql = JArrayHelper::getValue($searchtoken, 'join', null);
      $searchSql = JArrayHelper::getValue($searchtoken, 'search', null);
      $ordersSql = JArrayHelper::getValue($searchtoken, 'orders', null);
      
      $saved = ($dir === 'saved');
      $shared = ($dir === 'shared');
      $trashed = ($dir === 'trashed');

      $masteruser = PFactory::isMasterUser();
      $user = PFactory::getMasterUser();
      $db = PFactory::getDbo();

      $limitstart = 0;
      if(!$shared || $masteruser) {
         // below query to use ignore index to get rid of index_merge optimization which actually slows down the query 
         $query = "SELECT row " . 
                  " FROM (
                     SELECT rowa.id, @rownum:=@rownum+1 AS row
                      FROM (
                        SELECT s.* FROM *PREFIX*hr_jobseeker AS s IGNORE INDEX (is_trashed, company_id)" . 
                        " LEFT JOIN #__jobs_job AS j ON j.id=s.job_id " .
                        " LEFT JOIN #__jobs_employer AS e ON e.user_id=j.employer_id AND e.user_id=s.company_id" .
                        " LEFT JOIN *PREFIX*hr_degree_level AS degree_table ON degree_table.degree_level = s.highest_education_degree_level " .
                        " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1) 
                          LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON (rff.folder_id=rf.folder_id AND rff.jobseeker_id=s.id) " . $joinSql .
                        " WHERE s.id = " . intval($resumeid) . " OR (s.company_id = " . $user->id .
                        ($saved > 0 ? " AND rff.folder_id IS NOT NULL" : "") .
                        ($trashed > 0 ? " AND s.is_trashed = 1" : " AND s.is_trashed=0") . $searchSql . ")" .
                        " ORDER BY " . $ordersSql . ", s.job_id DESC, s.email DESC ) AS rowa, (SELECT @rownum:=0) AS rowb) AS rows " . 
                  " WHERE id=" . intval($resumeid); 
         $db->setQuery(PUtil::replaceOCPrefix($query)); 
         $rownum = $db->loadResult(); 
         if(!$rownum)
            return array(null, null);

         $limitstart = $rownum; 
         $query = "SELECT s.*, j.job_title, j.id as jobid, " .
                  " j.online_interview_requirements, " .
                  ($masteruser ? \OCP\PERMISSION_ALL . " as permissions " : "IF(jm.jobid IS NULL, " . \OCP\PERMISSION_READ . ", " . \OCP\PERMISSION_ALL . ") as permissions") .
                  " FROM *PREFIX*hr_jobseeker AS s IGNORE INDEX (is_trashed, company_id)" .
                  " LEFT JOIN #__jobs_job AS j ON j.id=s.job_id " .
                  " LEFT JOIN #__jobs_employer AS e ON e.user_id=j.employer_id AND e.user_id=s.company_id " .
                  " LEFT JOIN *PREFIX*hr_degree_level AS degree_table ON degree_table.degree_level = s.highest_education_degree_level " .
                  ($masteruser ? "" : " LEFT JOIN *PREFIX*hr_jobmanager AS jm ON (jm.jobid=s.job_id AND jm.manager=" . $db->quote(OC_User::getUser()) . ") ") .
                  " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1) 
                    LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON (rff.folder_id=rf.folder_id AND rff.jobseeker_id=s.id) " . $joinSql .
                  "  WHERE s.id = " . intval($resumeid) . " OR (s.company_id = " . $user->id .
                  ($saved > 0 ? " AND rff.folder_id IS NOT NULL" : "") .
                  ($trashed > 0 ? " AND s.is_trashed = 1" : " AND s.is_trashed=0") . $searchSql . ")" .
                  " ORDER BY " . $ordersSql . ", s.job_id DESC, s.email DESC ";
      }
      else {
         // look from share list 
         $query = "SELECT row " .
                  " FROM (
                     SELECT rowa.id, @rownum:=@rownum+1 AS row
                      FROM (
                        SELECT s.* " . 
                        "FROM *PREFIX*share AS a " .
                        " LEFT JOIN *PREFIX*hr_jobseeker AS s IGNORE INDEX (is_trashed, company_id) ON s.id=a.item_source " . 
                        " LEFT JOIN #__jobs_job AS j ON j.id=s.job_id " . 
                        " LEFT JOIN #__jobs_employer AS e ON e.user_id=j.employer_id AND e.user_id=s.company_id " .
                        " LEFT JOIN *PREFIX*groups_custom AS g ON g.groupname=a.share_with " . 
                        " LEFT JOIN *PREFIX*group_user_custom AS gu ON gu.gid=g.gid " . 
                        " LEFT JOIN *PREFIX*hr_degree_level AS degree_table ON degree_table.degree_level = s.highest_education_degree_level " .
                        " WHERE (a.share_with = " . $db->quote(OC_User::getUser()) .
                        "   OR (gu.uid=" . $db->quote(OC_User::getUser()) . " AND a.share_type=" . OCP\Share::SHARE_TYPE_GROUP . "))" .
                        " AND a.item_type='resumes' " . 
                        " AND e.user_id = " . $user->id . $searchSql .
                        " GROUP BY a.id ORDER BY " . $ordersSql . ", s.job_id DESC, s.email DESC) AS rowa, (SELECT @rownum:=0) AS rowb) AS rows " .
                  " WHERE id=" . intval($resumeid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $rownum = $db->loadResult();
         if(!$rownum)
            return array(null, null);

         $limitstart = $rownum;

         $query = "SELECT s.*, j.job_title, j.id as jobid, " .
                  " j.online_interview_requirements " .
                  " FROM `*PREFIX*share` AS a  " .
                  " LEFT JOIN *PREFIX*hr_jobseeker AS s IGNORE INDEX (is_trashed, company_id) ON s.id=a.item_source" .
                  " LEFT JOIN #__jobs_job AS j ON j.id=s.job_id " .
                  " LEFT JOIN #__jobs_employer AS e ON e.user_id=j.employer_id AND e.user_id=s.company_id " .
                  " LEFT JOIN *PREFIX*groups_custom AS g ON g.groupname=a.share_with " .
                  " LEFT JOIN *PREFIX*group_user_custom AS gu ON gu.gid=g.gid" .
                  " LEFT JOIN *PREFIX*hr_degree_level AS degree_table ON degree_table.degree_level = s.highest_education_degree_level " .
                  " WHERE (a.share_with = " . $db->quote(OC_User::getUser()) .
                  "       OR (gu.uid=" . $db->quote(OC_User::getUser()) . " AND a.share_type=" . OCP\Share::SHARE_TYPE_GROUP . "))" .
                  " AND a.item_type='resumes' " . 
                  " AND e.user_id = " . $user->id . $searchSql .
                  " ORDER BY " . $ordersSql . ", s.job_id DESC, s.email DESC ";
      }

      $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart, 1); 
      $nextresume  = $db->loadObject();

      $prevresume = null; 
      if($limitstart>=2) {
         $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart-2, 1);
         $prevresume = $db->loadObject();
      }

      return array($prevresume, $nextresume);
   }

   public function checkPermissions($resumes=array(), $permission=0) {
      if(PFactory::isMasterUser())
         return $resumes;
      
      $resumelist = array();
      // check subaccount admin permission
      $db = PFactory::getDbo();
      $query = "SELECT js.id FROM *PREFIX*hr_jobseeker AS js 
                LEFT JOIN *PREFIX*hr_jobmanager AS jm ON (jm.jobid=js.job_id AND jm.manager=" . $db->quote(OC_User::getUser()) . ")
                WHERE jm.jobid IS NOT NULL AND js.id IN (" . implode(",", $resumes) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $resumelist = $db->loadAssocList('', 'id');
      
      $resumes = array_diff($resumes, $resumelist);
      // check sharing permission setup 
      if(!empty($resumes)) {
         $query = "SELECT a.item_source, BIT_OR(permissions) AS permissions " . 
                  " FROM *PREFIX*share AS a " . 
                  " LEFT JOIN *PREFIX*groups_custom AS g ON g.groupname=a.share_with " .
                  " LEFT JOIN *PREFIX*group_user_custom AS gu ON gu.gid=g.gid" .
                  " WHERE a.item_source IN (" . implode(",", $resumes) . ")" . 
                  " AND item_type='resumes' " . 
                  " AND (a.share_with = " . $db->quote(OC_User::getUser()) .
                  "       OR (gu.uid=" . $db->quote(OC_User::getUser()) . " AND a.share_type=" . OCP\Share::SHARE_TYPE_GROUP . "))" .
                  " GROUP BY a.item_source";  
   
         $db->setQuery(PUtil::replaceOCPrefix($query)); 
         $list = $db->loadObjectList(); 
         foreach($list as $share) {
            if($share->permissions & $permission) 
               array_push($resumelist, $share->item_source); 
         }
      }

      return $resumelist; 
   }

   public function checkPermission($resume=0, $permission=0) {
      $resumelist = self::checkPermissions(array($resume), $permission); 
      if(in_array($resume, $resumelist))
         return true; 
   
      return false; 
   }

   public function getSharedResumeAsFiles($companyid, $resumeids) {
      JArrayHelper::toInteger($resumeids);
      $db = PFactory::getDbo();
      $query = "SELECT a.permissions, j.*, s.id as jobid, s.job_title as jobtitle, s.num_applied, s.online_interview_requirements, s.auto_invite, s.system as jobsystem, hjs.title AS status_title, jj.sheetid, js.totalscore AS jobtest_totalscore,ju.creator, " .
               " (SELECT COUNT(*) FROM *PREFIX*hr_jobseeker AS tj WHERE tj.is_trashed=0 AND tj.job_id=j.job_id AND tj.online_interview_testscore>j.online_interview_testscore) AS rank, " .
               " IF(s.auto_invite OR !hjt.auto_send, hjt.id, 0) AS token_id, hjt.create_date AS invite_date, " .
               " IF(rff.folder_id IS NULL, 0, 1) as is_saved" .
               " FROM `*PREFIX*share` AS a  " .
               " LEFT JOIN *PREFIX*hr_jobseeker AS j ON j.id=a.item_source" .
               " LEFT JOIN *PREFIX*hr_jobseeker_uploaded AS ju ON ju.jobseeker_id=j.id" .
               " LEFT JOIN #__jobs_job AS s ON s.id=j.job_id " .
               " LEFT JOIN #__jobs_employer AS e ON e.user_id=s.employer_id AND e.user_id=j.company_id " .
               " LEFT JOIN *PREFIX*hr_jobseeker_status AS hjs ON hjs.id=j.status_id " .
               " LEFT JOIN *PREFIX*groups_custom AS g ON g.groupname=a.share_with " .
               " LEFT JOIN *PREFIX*group_user_custom AS gu ON gu.gid=g.gid " .
               " LEFT JOIN #__jobtest_jobsheet AS jj ON jj.jobid=j.job_id " .
               " LEFT JOIN #__jobtest_sheet AS js ON js.id=jj.sheetid " .
               " LEFT JOIN *PREFIX*hr_jobseeker_token AS hjt ON hjt.jobseeker_id=j.id AND hjt.disable=0 " .
               " LEFT JOIN *PREFIX*hr_resume_folder AS rf ON (rf.folder_name='我的收藏' AND rf.owner=" . $db->quote(OC_User::getUser()) . " AND rf.system=1) 
                 LEFT JOIN *PREFIX*hr_resume_folder_files AS rff ON (rff.folder_id=rf.folder_id AND rff.jobseeker_id=j.id) " .
               " WHERE (a.share_with = " . $db->quote(OC_User::getUser()) .
               "       OR (gu.uid=" . $db->quote(OC_User::getUser()) . " AND a.share_type=" . OCP\Share::SHARE_TYPE_GROUP . "))" .
               " AND a.item_type='resumes' " .
               " AND j.id IN (" . implode(",", $resumeids) . ") AND e.user_id = " . intval($companyid); 

      /* try to load from cache */
      $cache = CacheHelper::getCache('app_resumes');
      // get cached data now 
      $data = $cache->call(array($this, '_getSharedResumeAsFiles'), $query);

      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user 
      if(CacheHelper::needTriggers($cache) && $data) {
         foreach($data as $resume) {
            $tables = array('*PREFIX*hr_jobseeker-'.$resume->id,
                            '*PREFIX*hr_jobseeker_uploaded-'.$resume->id,
                            '*PREFIX*hr_jobseeker_status-'.$resume->id,
                            '*PREFIX*hr_jobseeker_token-'.$resume->id,
                            '#__jobtest_sheet-'.$resume->sheetid,
                            '#__jobs_job-'.$resume->job_id, 
                            '*PREFIX*hr_company_interview-'.$resume->job_id,
                            '*PREFIX*share-'.$resume->id);
            CacheHelper::setTriggers(intval($companyid), $cache, $tables);
         }
      }

      return $data;

   }

   public function _getSharedResumeAsFiles($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObjectList(); 

   }

   /**
    * Get persontest result info
    *
    * @param   integer  $companyid  Company id
    * @param   integer  $jobseeker_id  Jobseeker id
    * @return  array  Persontest result array inlude 'alias', 'result', 'details'
    *
    * @since   3.0
    */
   public function getPersontestResultInfo($companyid, $jobseeker_id, $shortresult=false) {
      if (empty($companyid) && empty($jobseeker_id))
         return null;
      $db = PFactory::getDbo();    
      $query = "SELECT ps.title AS sheetTitle, ps.shorttitle AS sheetShortTitle, pr.result, pr.start_time, pr.end_time FROM #__persontest_result AS pr
                LEFT JOIN #__persontest_sheet AS ps ON ps.id=pr.sheetid
                LEFT JOIN #__persontest_jobsheet AS jpj ON jpj.sheetid=pr.sheetid
                LEFT JOIN #__users AS u ON u.id=pr.user_id
                LEFT JOIN *PREFIX*hr_jobseeker AS hj ON (hj.email=u.email AND hj.job_id=jpj.jobid)
                LEFT JOIN #__jobs_employer AS je ON je.user_id=hj.company_id
                WHERE je.user_id=".intval($companyid)." AND hj.id=".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $persontestResultInfo = $db->loadObject();
      if(!empty($persontestResultInfo)) {
         $persontestResultInfo->result = json_decode($persontestResultInfo->result, true);
         // process to generate short text result if needed 
         if ($shortresult) {
            $persontestResult = $persontestResultInfo->result;
            if (!empty($persontestResult)) {
               if (isset($persontestResult['alias']) && isset($persontestResult['result']) && isset($persontestResult['details'])) {
                  if (empty($persontestResult['result'])) {
                     $persontestResultInfo->shortresult = $persontestResultInfo->sheetShortTitle . ':性格特征不明显';
                  } else {
                     $pResult = $persontestResultInfo->sheetShortTitle . '：';
                     $sheetalias = $persontestResult['alias'];
                     $result     = $persontestResult['result'];
                     $details    = $persontestResult['details'];

                     switch ($sheetalias) {
                        case 'HOLLAND':
                           $patternArr = str_split($result);
                           $hAnswers = $this->getPersontestAnswer($sheetalias, $patternArr);
                           $aCount = count($hAnswers);
                           $find = array('（S）', '（E）', '（C）', '（R）', '（I）', '（A）');

                           foreach ($hAnswers as $key => $hAnswer) {
                              $pResult .= str_ireplace($find, '', $hAnswer->p_shorttitle).($key < ($aCount -1) ? '、' : '');
                           }
                           $persontestResultInfo->shortresult = $pResult; 
                        break;
                        default:
                           $persontestAnswer = $this->getPersontestAnswer($sheetalias, $result);
                           $pResult .= $persontestAnswer->p_shorttitle;
                           $persontestResultInfo->shortresult = $pResult; 
                        break;
                     }
                  }
               }
            }
         }
      }

      return $persontestResultInfo;
   }
    
   /**
    * Get persontest answer
    *
    * @param   string  $sheetalias  Persontest sheet alias
    * @param   mixed  $pattern  Pattern string or array
    * @return  mixed  Persontest result answer object or answers object list array
    *
    * @since   3.0
    */
   public function getPersontestAnswer($sheetalias, $pattern) {
      if (empty($pattern))
         return null;
   
      $db = PFactory::getDbo();
   
      if (is_array($pattern)) {
         $patternArr = array();
          
         foreach($pattern as $p)
         {
            $patternArr[] = $db->quote($p);
         }
          
         $query = "SELECT psp.title AS p_title, psp.shorttitle AS p_shorttitle, psp.text FROM #__persontest_sheet_patterns AS psp
                   LEFT JOIN #__persontest_sheet AS ps ON ps.id=psp.sheetid
                   WHERE ps.alias=".$db->quote($sheetalias)." AND psp.alias in (".implode(",", $patternArr).")"." AND psp.answer=1";
         $db->setQuery($query);
          
         return $db->loadObjectList();
      } else {
         $query = "SELECT psp.title AS p_title, psp.shorttitle AS p_shorttitle, psp.text FROM #__persontest_sheet_patterns AS psp
                   LEFT JOIN #__persontest_sheet AS ps ON ps.id=psp.sheetid
                   WHERE ps.alias=".$db->quote($sheetalias)." AND psp.alias=".$db->quote($pattern)." AND psp.answer=1";
         $db->setQuery($query);
          
         return $db->loadObject();
      }
   }

   /**
    * Function to get User's Paper Result (Only Objective Question)
    * 
    * @param  int $jobseeker_id  jobseeker's id
    * @param  int $sheetid       sheet id
    * @return object
    *
    * @since   3.0
    */
   public function getUserPaperResultObjective($jobseeker_id,$type,$sheetid) {
      if (empty($jobseeker_id) || empty($sheetid))
         return null;

      $where = $type=='wrong' ? "AND b.score = 0" : "";
      $db = PFactory::getDbo();
      $query = "SELECT a.id AS jobseeker_id, a.job_id, b.questionid AS question_id, b.sheetid, b.score AS user_score, b.answer AS user_answer, c.employer_id, d.score, e.typeid, e.difficulty, f.question, f.answer, g.type
         FROM *PREFIX*hr_jobseeker a
         LEFT JOIN #__jobtest_result b ON b.jobseeker_id = a.id
         LEFT JOIN #__jobs_job c ON c.id = a.job_id
         LEFT JOIN #__jobtest_sheetquestions d ON d.questionid = b.questionid
         LEFT JOIN #__jobtest_question e ON e.id = b.questionid
         LEFT JOIN #__jobtest_question_details f ON f.questionid = b.questionid
         LEFT JOIN #__jobtest_questiontype g ON g.id = e.typeid
         WHERE g.type <> 'freeinput' AND b.sheetid=".intval($sheetid).
            " AND a.id = ".intval($jobseeker_id).
            " AND b.jobseeker_id=".intval($jobseeker_id).
            " ".$where.
            " GROUP BY b.questionid";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObjectList();

      return $result;
   }

   /**
    * Function to get User's Paper Result(only freeinput Question)
    *
    * @param  int $jobseeker_id  jobseeker's id
    * @param  int $sheetid       sheet id
    * @return object
    *
    * @since   3.0
    */
   public function getUserPaperResultFreeinput($jobseeker_id, $sheetid) {
      if (empty($jobseeker_id) || empty($sheetid))
         return null;

      $db = PFactory::getDbo();
      $query = "SELECT a.id AS jobseeker_id, a.job_id, b.questionid AS question_id, b.sheetid, b.score AS user_score, b.answer AS user_answer, c.employer_id, d.score, e.typeid, e.difficulty AS q_difficulty, f.question, f.answer
         FROM *PREFIX*hr_jobseeker a
         LEFT JOIN #__jobtest_result b ON b.jobseeker_id = a.id
         LEFT JOIN #__jobs_job c ON c.id = a.job_id
         LEFT JOIN #__jobtest_sheetquestions d ON d.questionid = b.questionid
         LEFT JOIN #__jobtest_question e ON e.id = b.questionid
         LEFT JOIN #__jobtest_question_details f ON f.questionid = b.questionid
         LEFT JOIN #__jobtest_questiontype g ON g.id = e.typeid
         WHERE g.type='freeinput' AND b.sheetid=".intval($sheetid).
            " AND a.id = ".intval($jobseeker_id).
            " AND b.jobseeker_id=".intval($jobseeker_id).
            " GROUP BY b.questionid";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObjectList();

      return $result;
   }

   /**
    * Get Basic Info for paper review use
    *
    * @param  int $jobseeker_id  Jobseeker id from $_GET
    * @return object
    *
    * @since 3.0
    */
   public function getPaperBasicInfo($jobseeker_id) {
      if (empty($jobseeker_id))
         return null;

      $db = PFactory::getDbo();
      $query = "SELECT a.id, a.job_id AS jobid, a.name, a.is_reviewed, a.online_interview_testscore, b.job_title AS jobtitle, d.id AS sheetid, d.title AS paper_title, d.totalscore, d.questioncount, d.totaltime, e.accept_date, e.finished_date, b.num_applied
         FROM *PREFIX*hr_jobseeker a
         LEFT JOIN #__jobs_job b ON b.id=a.job_id
         LEFT JOIN #__jobtest_result c ON c.jobseeker_id=a.id
         LEFT JOIN #__jobtest_sheet d ON d.id=c.sheetid
         LEFT JOIN *PREFIX*hr_jobseeker_token e ON e.jobseeker_id=a.id
         WHERE a.id = ".intval($jobseeker_id);

      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadObject();
   }

   /**
    * Get test ranking by jobid
    * @param  int $jobid job id
    * @param  int $score jobseeker's total score
    * @return  mixed  bool or int
    *
    * @since 3.2
    */
   public function getTestRank($jobid, $score) {
      if (empty($jobid) || !isset($score) || $score == null)
         return false;

      $db = PFactory::getDbo();
      $query = "SELECT COUNT(*) + 1
         FROM *PREFIX*hr_jobseeker
         WHERE is_trashed = 0 AND job_id = ".intval($jobid)." AND online_interview_testscore > ".intval($score);

      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadResult();
   }

   /**
    * Get Correct Rate Status
    *
    * @param  int $jobseeker_id  jobseeker's id
    * @param  int $sheetid       sheet id
    * @return mixed
    *
    * @since  3.0
    */
   public function getCorrectRate($jobseeker_id, $sheetid=0) {
      if (empty($jobseeker_id) || empty($sheetid))
         return null;

      $db = PFactory::getDbo();
      $query = "SELECT b.questionid AS question_id, e.type, c.score, b.score AS answer_score
         FROM *PREFIX*hr_jobseeker a
         LEFT JOIN #__jobtest_result b ON b.jobseeker_id = a.id
         LEFT JOIN #__jobtest_sheetquestions c ON c.questionid = b.questionid
         LEFT JOIN #__jobtest_question d ON d.id = b.questionid
         LEFT JOIN #__jobtest_questiontype e ON e.id = d.typeid
         WHERE b.sheetid=".intval($sheetid)." AND c.sheetid=".intval($sheetid)." AND a.id = ".intval($jobseeker_id)." AND b.jobseeker_id=".intval($jobseeker_id)." GROUP BY b.questionid";

      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObjectList();

      $c_rate = array();
      $c_rate['singleanswer_correct_count'] = 0;
      $c_rate['singleanswer_total_count'] = 0;
      $c_rate['singleanswer_score'] = 0;
      $c_rate['singleanswer_total_score'] = 0;
      $c_rate['freeinput_score'] = 0;
      $c_rate['freeinput_total_score'] = 0;
      $c_rate['total_correct_score'] = 0;
      foreach ($result as $k => $v) {
         switch ($v->type) {
            case 'freeinput':
               $c_rate['freeinput_score'] += $v->answer_score;
               $c_rate['freeinput_total_score'] += $v->score;
               $c_rate['total_correct_score'] += $v->answer_score;
               break;
            case 'singleanswer':
               if ($v->answer_score > 0)
                  $c_rate['singleanswer_correct_count']++;

               $c_rate['singleanswer_total_count']++;
               $c_rate['singleanswer_score'] += $v->answer_score;
               $c_rate['singleanswer_total_score'] += $v->score;
               $c_rate['total_correct_score'] += $v->answer_score;
               break;
            default:
               if ($v->answer_score > 0)
                  $c_rate['singleanswer_correct_count']++;

               $c_rate['singleanswer_total_count']++;
               $c_rate['singleanswer_score'] += $v->answer_score;
               $c_rate['singleanswer_total_score'] += $v->score;
               $c_rate['total_correct_score'] += $v->answer_score;
               break;
         }
      }

      return $c_rate ? $c_rate : null;
   }

   /**
    * Post paper Score
    *
    * @param  array $post each freeinput's answer
    * @return bool
    *
    * @since 3.0
    */
   public function postScore($post) {
      if (!PSession::checkToken('post')) {
         $this->setError('登录已失效，请重新登录');
         return false;
      }
      if (empty($post)) {
         $this->setError('非正常提交！');
         return false;
      }
      if (empty($post['jobid']) || empty($post['jobseeker_id']) || empty($post['sheetid'])) {
         $this->setError('非正常提交！');
         return false;
      }
      
      $hasPermission = false;
      if(!OC_User::isLoggedIn()) {
         $app = PFactory::getApplication();
         $companyid = $app->getUserState('resumeshare_companyid', 0);
         if($companyid) {
            $hasPermission = true;
         }
      } elseif (PCompanyHelper::isJobManager() && (PCompanyHelper::checkJobManagerPermissions($post['jobid']) || PResumesHelper::isSharedResume(OC_User::getUser(), $post['jobseeker_id']))) {
         $hasPermission = true;
      }
      
      if (!$hasPermission) {
         $this->setError('您无权访问该资源！');
         return false;
      }

      $jobseeker_id = $post['jobseeker_id'];
      $sheetid = $post['sheetid'];

      unset($post['jobseeker_id'],$post['requesttoken'],$post['sheetid']);

      $db = PFactory::getDbo();
      $query = "SELECT b.questionid AS qid, c.score, b.score AS user_score
         FROM *PREFIX*hr_jobseeker a
         LEFT JOIN #__jobtest_result b ON b.jobseeker_id = a.id
         LEFT JOIN #__jobtest_sheetquestions c ON c.questionid = b.questionid
         LEFT JOIN #__jobtest_question d ON d.id = b.questionid
         LEFT JOIN #__jobtest_questiontype e ON e.id = d.typeid
         WHERE e.type='freeinput' AND b.sheetid=".intval($sheetid).
            " AND a.id = ".intval($jobseeker_id).
            " AND b.jobseeker_id=".intval($jobseeker_id).
            " GROUP BY b.questionid";

      $db->setQuery(PUtil::replaceOCPrefix($query));
      $questions = $db->loadObjectList();

      $valuesArr = array();
      foreach ($questions as $k => $q) {
         if ($post[$q->qid] == null) {
            $this->setError('请完成所有主观题打分！');
            return false;
         }
         $post[$q->qid] = intval($post[$q->qid]);
         if ($post[$q->qid] < 0) {
            $this->setError('您的打分不应为负数！');
            return false;
         }
         if ($post[$q->qid] > $q->score) {
            $this->setError('您的打分超过了题目分值！');
            return false;
         }
         if (isset($post[$q->qid])) {
            $valuesArr[] = "('".intval($q->qid)."', '".intval($jobseeker_id)."', '".intval($post[$q->qid])."')";
         }
      }

      // Update score
      $valuesSql = implode(",", $valuesArr);
      $query = "INSERT INTO #__jobtest_result
         (`questionid`, `jobseeker_id`, `score`)
         VALUES ".$valuesSql.
         "ON DUPLICATE KEY UPDATE score = VALUES(score)";
      $db->setQuery($query);
      if (!$db->execute()) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      // Update review status
      $query = "UPDATE *PREFIX*hr_jobseeker SET is_reviewed = 1 WHERE id=".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      // Update total score
      $query = "UPDATE *PREFIX*hr_jobseeker SET online_interview_testscore = (SELECT SUM(score) FROM #__jobtest_result WHERE jobseeker_id = ".intval($jobseeker_id).") WHERE id=".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      // stay on master for match degree update 
      $db->stayOnMaster();
      // Update matching degree
      $query = "SELECT job_id, token
         FROM *PREFIX*hr_jobseeker_token a
         LEFT JOIN *PREFIX*hr_jobseeker b ON b.id = a.jobseeker_id
         WHERE a.jobseeker_id = ".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObject();

      $success = PInterviewHelper::updateMatchDegree($result->token, intval($jobseeker_id), intval($result->job_id));
      CacheHelper::triggerRemove(PFactory::getMasterUser()->id, array('*PREFIX*hr_jobseeker', '*PREFIX*hr_jobseeker-' . intval($jobseeker_id)));

      // leave master enforcement now 
      $db->leaveOnMaster();

      if ($success === false) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      return true;
   }

   /**
    * Get matching details
    *
    * @param  integer $resumeid jobseeker id
    * @param  integer $companyid company id
    * @return  mixed
    *
    * @since 3.0
    */
   public function getMatchDetail($resumeid=0, $companyid=0) {
      if (empty($resumeid))
         return null;
      if (empty($companyid)) {
         $user = PFactory::getMasterUser();
         $companyid = $user->id;
         if (empty($companyid))
            return null;
      }

      $interview_progess = PInterviewHelper::getInterviewProgress($resumeid, $companyid);
      if (empty($interview_progess))
         return null;

      $db = PFactory::getDbo();
      $query = "SELECT DISTINCT a.job_id, c.id, c.text_hr, c.choices, c.alias, c.match_rule, d.unit_name AS unit, b.expected_answer, e.answer, f.industry AS expected_industry, g.industry AS answer_industry
               FROM *PREFIX*hr_jobseeker a
               LEFT JOIN *PREFIX*hr_company_interview b ON b.job_id = a.job_id
               LEFT JOIN *PREFIX*hr_interview_questions c ON (c.id = b.interview_id OR c.alias = 'MORESKILL')
               LEFT JOIN *PREFIX*hr_interview_questions_unit d ON d.id = c.unit_id
               LEFT JOIN *PREFIX*hr_jobseeker_interview e ON e.interview_id=c.id AND e.jobseeker_id=a.id
               LEFT JOIN #__jobs_industry f ON f.id = b.expected_answer
               LEFT JOIN #__jobs_industry g ON g.id = e.answer
               LEFT JOIN #__jobs_employer h ON h.user_id = a.company_id
               WHERE c.alias <> 'SCHOOL_LEVEL' AND a.id = ".intval($resumeid)." AND h.user_id = ".intval($companyid)." GROUP BY e.id ORDER BY c.seq";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $return = $db->loadObjectList();

      // If this resume is not belong to login company
      if (empty($return))
         return null;

      $job_id = $return[0]->job_id;
      $hasperm = PCompanyHelper::checkJobManagerPermissions($job_id);

      // Get each matching degree
      $query = "SELECT details
            FROM *PREFIX*hr_jobseeker_match_details
            WHERE jobseeker_id = ".intval($resumeid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $details = json_decode($db->loadResult(), true);

      $match_rule = array('GREATER'=>'以上','LESS'=>'以下','RANGE'=>'之间','EQUAL'=>'');
      foreach ($return as $k => $q) {
         if ($q->alias == 'SALARY' && !$hasperm) {
            unset($return[$k]);
            continue;
         }

         if (!empty($q->choices)) {
            $choices = json_decode($q->choices, true);

            if ($q->alias == 'SKILL') {
               $skilllevel = '';
               if(is_numeric($q->expected_answer)){
                  $q->expected_answer = $skilllevel.' '.$q->expected_answer.'个'.$q->unit.$match_rule[$q->match_rule];
               }else{
                  $expected_answer = json_decode($q->expected_answer);
                  if(!empty($expected_answer->level)){
                     $level = $expected_answer->level;
                     if($level == PInterviewHelper::SKILL_LEVEL_KNOW){
                        $level = PInterviewHelper::SKILL_LEVEL_GENERALE;
                     }
                     $skilllevel = $choices[$level];
                  }
                  $q->expected_answer = $skilllevel.' '.(empty($expected_answer->exp)?'':$expected_answer->exp.'个'.$q->unit.$match_rule[$q->match_rule]);
               }
               $userskilllevel = '';
               if(!empty($q->answer) && is_numeric($q->answer)){
                  if($q->answer == 0){
                     $q->answer = '无';
                  }else{
                     $q->answer = $userskilllevel.' '.$q->answer.'个'.$q->unit;
                  }
               }else{
                  $userexpected_answer = json_decode($q->answer);
                  if(!empty($userexpected_answer->level)){
                     $userlevel = $userexpected_answer->level;
                     if($userlevel == PInterviewHelper::SKILL_LEVEL_KNOW){
                        $userlevel = PInterviewHelper::SKILL_LEVEL_GENERALE;
                     }
                     $userskilllevel = $choices[$userlevel];
                  }else{
                     $userskilllevel = '无';
                  }
                  $q->answer = $userskilllevel.' '.(empty($userexpected_answer->exp) || !is_numeric($userexpected_answer->exp)?'':intval($userexpected_answer->exp)).'个'.$q->unit;
               }
            } elseif ($q->alias == 'MARRIAGE') {
               $q->expected_answer = $choices[$q->expected_answer];
               if(!empty($q->answer)){
                  $q->answer = $choices[$q->answer];
               }
            } elseif ($q->alias == 'MORESKILL') {
               $moreskillname = $moreskilllevel = '';
               $moreskillexp = 0;
               if(!empty($q->answer)){
               $useranswer = json_decode($q->answer);
               if(isset($useranswer->skill)){
                  $moreskillname = $useranswer->skill;
               }
               if(!empty($useranswer->level)){
                  $level = $useranswer->level;
                  $moreskilllevel = $choices[$level];
               }
               if(!empty($useranswer->exp)){
                  $moreskillexp = $useranswer->exp;
               }
               $q->skill = $moreskillname;
               $q->level = $moreskilllevel;
               $q->exp = $moreskillexp;
               }
            } elseif ($q->alias == 'LANG') {
               $q->expected_answer = $choices[$q->expected_answer].$match_rule[$q->match_rule];
               $q->answer = empty($q->answer) ? '不会' : (isset($choices[$q->answer]) ? $choices[$q->answer] : null);
            } elseif ($q->alias == 'LOCATION') {
               $q->answer = isset($choices[$q->answer]) ? $choices[$q->answer] : null;
            }
            elseif ($q->alias == 'ENTRY_DEADLINE') {
               $q->expected_answer = $choices[$q->expected_answer];
               $q->answer = isset($choices[$q->answer]) ? $choices[$q->answer] : null;
            }else{
               $q->expected_answer = $choices[$q->expected_answer].$match_rule[$q->match_rule];
               $q->answer = isset($choices[$q->answer]) ? $choices[$q->answer] : null;
            }
         } else {
            $q->expected_answer = empty($q->unit) ? $q->expected_answer.$match_rule[$q->match_rule] : $q->expected_answer.$q->unit.$match_rule[$q->match_rule];
            $q->answer = empty($q->unit) ? $q->answer : $q->answer.$q->unit;
         }
         if ($q->alias == 'INDUSTRY') {
            $q->expected_answer = JText::_($q->expected_industry);
            if($q->answer_industry == '' || $q->answer==0)
               $q->answer = '应届毕业生';
            else
               $q->answer = JText::_($q->answer_industry);
         }
         if ($q->alias == 'WORK_EXP') {
            $q->answer = $q->answer%12 > 0 ? floor($q->answer/12)."年".($q->answer%12)."个月" : floor($q->answer/12)."年";
         }
         if ($q->alias == 'AGE')
            $q->answer = $q->answer."岁";

         // Add match degree to each question
         if (isset($details[$q->id]))
            $q->match_degree = $details[$q->id];
         else
            $q->match_degree = "--";

         // unset unnecessary content
         unset($q->choices,$q->match_rule,$q->unit,$q->id,$q->expected_industry,$q->answer_industry);
      }

      // If has jobtest get score and total score
      if (isset($interview_progess['onlinetest'])) {
         $query = "SELECT c.totalscore AS expected_answer,
               (SELECT SUM(score) FROM #__jobtest_result WHERE jobseeker_id=a.id) AS answer
               FROM *PREFIX*hr_jobseeker a
               LEFT JOIN #__jobtest_jobsheet b ON b.jobid = a.job_id
               LEFT JOIN #__jobtest_sheet c ON c.id = b.sheetid
               WHERE a.id = ".intval($resumeid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $rs = $db->loadObject();
         $rs->text_hr = "线上笔试";
         $rs->alias = "";
         $rs->expected_answer = $rs->expected_answer."分";
         $rs->answer = $rs->answer."分";
         $rs->match_degree=0;
         if(!empty($details['onlinetest'])){
            $rs->match_degree = $details['onlinetest'];
         }
         
         array_push($return, $rs);
      }

      return $return;
   }
   
   // send sms
   public function sendSMSMessage($user){
      if($user){
         include_once(JPATH_SITE.'/components/com_jobmail/helpers/queue.php');
         
         $variables = array();
         $variables['action'] =  'sms';
         $variables['user'] = $user;
         $variables['tpl_type'] = 'hrinvite';
         /* put data into queue for further processing */
         try {
            $queue = new QueueHelper(array('host'=>'127.0.0.1'));
            $queue->putTask($variables, 'emailTube');
         } catch(Exception $e) {
            /* ignore connection failure error: */
            /* fsockopen 500 Socket error 111: Connection refused  connecting to 127.0.0.1:11300 */
            /* this error might occur if beanstalkd is not running */
         }
      }
   }
   
   // send email
   public function sendEmailMessage($user,$upload=false){
      if($user && !isFakeEmail($user['email'])){       
         require_once JPATH_SITE.'/components/com_jobmail/models/email.php';
         $mailer = new JobmailModelEmail();
         $config = JFactory::getConfig();
         $url = JURI::base().'index.php/apps/interview?token='.$user['token'];
         $joburl = JURI::base().'index.php/apps/jobposition/view?id='.$user['job_id'];
         $giveup_url = JURI::base().'index.php/apps/interview/giveup?token='.$user['token'].'&email='.$user['email'];
         $supportmail = $config->get('mailfrom','');
         

         $receiver = (isDevMode() || isTestMode() || isTestUser($user['company_email'])) ? $config->get('jobseekeremail', '') : $user['email'];
         $requirements = json_decode($user['online_interview_requirements'], true);
         if (count($requirements) <= 0)
            return;
         if(!empty($upload)){
            $auto_invite = $user['auto_invite'];
            if(empty($auto_invite))
               return;
            //upload resume's job has selected and auto invite and only set match condition
            if(count($requirements) == 1){
               $body='
                   <tr>
	                  <td align="center" class="bs-page-wrap">
	                     <table width="950" border="0" cellspacing="0" cellpadding="0" class="bs-wrap" style="margin: 0; border: 0; padding: 0; color: #444; text-align: left; font: 15px \'Helvetica Neue\', Helvetica, Arial, sans-serif; line-height: 1.6;">
	                        <tr>
	                           <td>
	                              <div class="companyname">'.$user['company'].'</div>
	                           </td>
	                        </tr>
	                        <tr>
	                           <td class="bs-content">
	                              <div style="padding:0 8px;">
	                                 <p class="p_title"><b>'.$user['jobtitle'].'职位邀请您补充简短信息</b></p>
	                              </div>
	                              <div style="padding:15px 25px 0 25px;">
	                                 <p class="p_text">亲爱的 <b style="color:#ff6600;">'. $user['name'] .'</b>，您好！</p>
	                                 <p class="p_text t_intent">我是 <b>'.$user['company'].'公司</b> 的HR，目前我司正在招聘 <b>'.$user['jobtitle'].'</b> <a href="'.$joburl.'" style="margin-left:0px;">(查看职位详情)</a>，发现了您的简历，您的技能和经验非常符合该职位的需求。</p>
	                                 <p class="p_text t_intent">现诚邀请您补充简短信息，如果符合职位要求，我们将进一步与您联系。</p>
	                                 <p class="p_text t_intent">如您有意向，请点击以下链接开始：</p>
	                                 <p class="p_text t_intent"><a href="'.$url.'" style="margin-left:0px;">'.$url.'</a></p>
	                                 <p class="p_text t_intent"><b style="color:#3f8e20;">这是一封系统邮件，并非面试邀请，如HR已与您电话联系，请遵循电话沟通约定，可忽略此邮件。</b></p>
	                                 <p class="p_text t_intent" style="padding:4px 0 0px 0px;">如有疑问，请联系 <b>枇杷派客服 4006-747247</b>  <a href="mailto:support@pipapai.com">support@pipapai.com</a></p>
	                                 <p class="p_text t_intent" style="padding:10px 0 30px 0px;"><a href="'.$giveup_url.'" style="margin-left:0px; text-indent:0em; display:inline-block; padding:0 15px 0 15px; height:40px; line-height:40px; background:#076C9B; color:#ffffff; text-decoration:none; text-align:center;">如您对此职位无兴趣，点击此处</a></p>
                                 </div>
	                           </td>
	                        </tr>
	                     </table>
	                  </td>
	               </tr>';
               $subject = $user['company'].' '.$user['jobtitle']."职位邀请您补充简短信息";
               //upload resume's job has selected and auto invite and not only set match condition,but also set person or written test
            }elseif(count($requirements) >= 1){
               $types = array('onlinetest'=>'线上笔试',  'persontest'=>'性格评测', 'onlinevideo'=>'视频面试');
               $requirelist = array_intersect_key($types, $requirements);
               $type = $types[key($requirelist)];
               $requiretexts = implode("，", array_values($requirelist));
               $body='
	                <tr>
	                  <td align="center" class="bs-page-wrap">
	                     <table width="950" border="0" cellspacing="0" cellpadding="0" class="bs-wrap" style="margin: 0; border: 0; padding: 0; color: #444; text-align: left; font: 15px \'Helvetica Neue\', Helvetica, Arial, sans-serif; line-height: 1.6;">
	                        <tr>
	                           <td>
	                              <div class="companyname">'.$user['company'].'</div>
	                           </td>
	                        </tr>
	                        <tr>
	                           <td class="bs-content">
	                              <div style="padding:0 8px;">
	                                 <p class="p_title"><b>'.$user['jobtitle'].'职位邀请您完成'.$type.'</b></p>
	                              </div>
	                              <div style="padding:15px 25px 0 25px;">
	                                 <p class="p_text">亲爱的 <b style="color:#ff6600;">'. $user['name'] .'</b>，您好！</p>
	                                 <p class="p_text t_intent">我是 <b>'.$user['company'].'公司</b> 的HR，目前我司正在招聘 <b>'.$user['jobtitle'].'</b> <a href="'.$joburl.'" style="margin-left:0px;">(查看职位详情)</a>，发现了您的简历，您的技能和经验非常符合该职位的需求。</p>
	                                 <p class="p_text t_intent">现诚邀您完成'.$requiretexts.'，如果符合职位要求，我们将进一步与您联系。</p>
	                                 <p class="p_text t_intent">如您有意向，请点击以下链接开始：</p>
	                                 <p class="p_text t_intent"><a href="'.$url.'" style="margin-left:0px;">'.$url.'</a></p>
	                                 <p class="p_text t_intent"><b style="color:#3f8e20;">这是一封系统邮件，并非面试邀请，如HR已与您电话联系，请遵循电话沟通约定，可忽略此邮件。</b></p>
	                                 <p class="p_text t_intent" style="padding:4px 0 0px 0px;">如有疑问，请联系 <b>枇杷派客服 4006-747247</b>  <a href="mailto:support@pipapai.com">support@pipapai.com</a></p>
	                                 <p class="p_text t_intent" style="padding:10px 0 30px 0px;"><a href="'.$giveup_url.'" style="margin-left:0px; text-indent:0em; display:inline-block; padding:0 15px 0 15px; height:40px; line-height:40px; background:#076C9B; color:#ffffff; text-decoration:none; text-align:center;">如您对此职位无兴趣，点击此处</a></p>
                                 </div>
	                           </td>
	                        </tr>
	                     </table>
	                  </td>
	                  </tr>';
               $subject = $user['company'].' '.$user['jobtitle']."职位邀请您完成".$type;
            }
         }else{
            if(count($requirements) == 1){
               $body='
                <tr>
                  <td align="center" class="bs-page-wrap">
                     <table width="950" border="0" cellspacing="0" cellpadding="0" class="bs-wrap" style="margin: 0; border: 0; padding: 0; color: #444; text-align: left; font: 15px \'Helvetica Neue\', Helvetica, Arial, sans-serif; line-height: 1.6;">
                        <tr>
                           <td>
                           <div class="companyname">'.$user['company'].'</div>
                           </td>
                        </tr>
                        <tr>
                           <td class="bs-content">
                              <div style="padding:0 8px;">
                                 <p class="p_title"><b>'.$user['jobtitle'].'职位已收到您的简历，邀请您补充简短信息</b></p>
                              </div>
                              <div style="padding:15px 25px 0 25px;">
                                 <p class="p_text">亲爱的 <b style="color:#ff6600;">'. $user['name'] .'</b>，您好！</p>
                                 <p class="p_text t_intent">我公司 <b>'.$user['jobtitle'].'</b> 职位邀请您补充简短信息，尚未收到您的反馈。</p>
                                 <p class="p_text t_intent">为了帮助HR更多了解您的背景，请您尽快补充简短基本信息。如果符合职位要求，HR会进一步与您联系。请点击以下链接开始：</p>
                                 <p class="p_text t_intent"><a href="'.$url.'" style="margin-left:0px;">'.$url.'</a></p>
                                 <p class="p_text t_intent"><b style="color:#3f8e20;">这是一封系统邮件，并非面试邀请，如HR已与您电话联系，请遵循电话沟通约定，可忽略此邮件。</b></p>
                                 <p class="p_text t_intent" style="padding:4px 0 0px 0px;">如有疑问，请联系 <b>枇杷派客服 4006-747247</b>  <a href="mailto:support@pipapai.com">support@pipapai.com</a></p>
                                 <p class="p_text t_intent" style="padding:10px 0 30px 0px;"><a href="'.$giveup_url.'" style="margin-left:0px; text-indent:0em; display:inline-block; padding:0 15px 0 15px; height:40px; line-height:40px; background:#076C9B; color:#ffffff; text-decoration:none; text-align:center;">如您对此职位无兴趣，点击此处</a></p>
                              </div>
                           </td>
                        </tr>
                     </table>
                  </td>
               </tr>';
               $subject = $user['company'].' '.$user['jobtitle']."职位收到了您的简历，邀请您补充简短信息";
            }else{
               $types = array('onlinetest'=>'线上笔试',  'persontest'=>'性格评测', 'onlinevideo'=>'视频面试');
            
               $requirelist = array_intersect_key($types, $requirements);
               $type = $types[key($requirelist)];
               $requiretexts = implode("，", array_values($requirelist));
            
               if (strpos($user['source'],'upload')===FALSE) {
                  $body='
	                <tr>
	                  <td align="center" class="bs-page-wrap">
	                     <table width="950" border="0" cellspacing="0" cellpadding="0" class="bs-wrap" style="margin: 0; border: 0; padding: 0; color: #444; text-align: left; font: 15px \'Helvetica Neue\', Helvetica, Arial, sans-serif; line-height: 1.6;">
	                        <tr>
	                           <td>
	                              <div class="companyname">'.$user['company'].'</div>
	                           </td>
	                        </tr>
	                        <tr>
	                           <td class="bs-content">
	                              <div style="padding:0 8px;">
	                                 <p class="p_title"><b>'.$user['jobtitle'].'职位邀请您完成'.$type.'</b></p>
	                              </div>
	                              <div style="padding:15px 25px 0 25px;">
	                                 <p class="p_text">亲爱的 <b style="color:#ff6600;">'. $user['name'] .'</b>，您好！</p>
	                                 <p class="p_text t_intent">感谢您申请我公司 <b>'.$user['jobtitle'].'</b> 职位，HR已查阅您的简历，为了进一步了解您的背景，请您尽快完成'.$requiretexts.'，如果符合职位要求，HR会进一步联系您。请点击以下链接开始：</p>
	                                 <p class="p_text t_intent"><a href="'.$url.'" style="margin-left:0px;">'.$url.'</a></p>
	                                 <p class="p_text t_intent" style="padding-top:4px;"><b style="color:#3f8e20;">这是一封系统邮件，并非面试邀请，如HR已与您电话联系，请遵循电话沟通约定，可忽略此邮件。</b></p>
	                                 <p class="p_text t_intent">如有疑问，请联系 <b>枇杷派客服 4006-747247</b>  <a href="mailto:support@pipapai.com">support@pipapai.com</a></p>
	                                 <p class="p_text t_intent" style="padding:10px 0 30px 0px;"><a href="'.$giveup_url.'" style="margin-left:0px; text-indent:0em; display:inline-block; padding:0 15px 0 15px; height:40px; line-height:40px; background:#076C9B; color:#ffffff; text-decoration:none; text-align:center;">如您对此职位无兴趣，点击此处</a></p>
                                 </div>
	                           </td>
	                        </tr>
	                     </table>
	                  </td>
	                  </tr>';
               } else {
                  $body='
	                <tr>
	                  <td align="center" class="bs-page-wrap">
	                     <table width="950" border="0" cellspacing="0" cellpadding="0" class="bs-wrap" style="margin: 0; border: 0; padding: 0; color: #444; text-align: left; font: 15px \'Helvetica Neue\', Helvetica, Arial, sans-serif; line-height: 1.6;">
	                        <tr>
	                           <td>
	                              <div class="companyname">'.$user['company'].'</div>
	                           </td>
	                        </tr>
	                        <tr>
	                           <td class="bs-content">
	                              <div style="padding:0 8px;">
	                                 <p class="p_title"><b>'.$user['jobtitle'].'职位邀请您完成'.$type.'</b></p>
	                              </div>
	                              <div style="padding:15px 25px 0 25px;">
	                                 <p class="p_text">亲爱的 <b style="color:#ff6600;">'. $user['name'] .'</b>，您好！</p>
	                                 <p class="p_text t_intent">我是 <b>'.$user['company'].'公司</b> 的HR，目前我司正在招聘 <b>'.$user['jobtitle'].'</b> <a href="'.$joburl.'" style="margin-left:0px;">(查看职位详情)</a>，发现了您的简历，您的技能和经验非常符合该职位的需求。</p>
	                                 <p class="p_text t_intent">现诚邀您完成'.$requiretexts.'，如果符合职位要求，我们将进一步与您联系。</p>
	                                 <p class="p_text t_intent">如您有意向，请点击以下链接开始：</p>
	                                 <p class="p_text t_intent"><a href="'.$url.'" style="margin-left:0px;">'.$url.'</a></p>
	                                 <p class="p_text t_intent"><b style="color:#3f8e20;">此邮件并非面试邀请，如HR已与您电话联系，请遵循电话沟通约定。</b></p>
	                                 <p class="p_text t_intent" style="padding:4px 0 0px 0px;">如有疑问，请联系 <b>枇杷派客服 4006-747247</b>  <a href="mailto:support@pipapai.com">support@pipapai.com</a></p>
                                    <p class="p_text t_intent" style="padding:10px 0 30px 0px;"><a href="'.$giveup_url.'" style="margin-left:0px; text-indent:0em; display:inline-block; padding:0 15px 0 15px; height:40px; line-height:40px; background:#076C9B; color:#ffffff; text-decoration:none; text-align:center;">如您对此职位无兴趣，点击此处</a></p>
	                              </div>
	                           </td>
	                        </tr>
	                     </table>
	                  </td>
	                  </tr>';
               }
               $subject = $user['company'].' '.$user['jobtitle']."职位邀请您完成".$type;
            }
         }
         $mailer->setTemplate('interview');
         $mailer->setVariables(array(
               'recipient'=>$receiver,
               'source'=>$user['source'],
               'jobsource'=>$user['jobsource'],
               'fromName'=>'枇杷派云招聘',
               'subject'=>$subject,
               'jobseeker'=>$user['name'],
               'supportmail'=>$supportmail,
               'hrurl'=>'http://'.OC_Request::serverHost(),
               'body'=>$body
         ));
   
         $mailer->send();
      }
   }
   
   /**
    * 判断简历是否分配职位或者设置笔试评测
    * @return boolean
    */
   public function checkInviteOnLineInterviewRequirements() {
      $resumeids = json_decode(JRequest::getString("ids", "[]"),true);
      $query = "SELECT js.id, jj.id AS job_id, jj.online_interview_requirements
               FROM *PREFIX*hr_jobseeker AS js
               LEFT JOIN  #__jobs_job AS jj ON js.job_id = jj.id
               WHERE js.id IN (" . implode(",", $resumeids) . ")";
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $datas = $db->loadAssocList();
      if(empty($datas)) {
         $this->setError("不存在该简历");
         return false;
      }
      foreach ($datas as $data) {
         if(!$data["job_id"]) {
            $this->setError("简历未分配职位，无法发送邀请");
            return false;
         }
         $tmp_online_interview_requirements_count = count(json_decode($data["online_interview_requirements"], true));
         if($tmp_online_interview_requirements_count <= 1) {
            $this->setError("职位未设笔试评测，无法发送邀请");
            return false;
         }
      }
      return true;
   }
   
   /**
    * Invite jobseeker by email & sms
    *
    * @return  boolean
    * @param $type: add this parameter in order to if hr upload a selected resume,
    * first send a email add a token or not send a email not add a token,
    * after edit a resume select a new job send new email,so not check if has a token
    * @since 3.2
    */
   public function inviteJobseeker($companyid = 0,$resumeids="",$upload=false,$type='add') {
      if(empty($resumeids)){
         $resumeids = json_decode(JRequest::getString("ids", "[]"));
      }
      if(empty($companyid) || empty($resumeids))
         return true;
      $sendsms = JRequest::getString("sendsms", "");
      $data = array();
      $db = PFactory::getDbo();
      JArrayHelper::toInteger($resumeids);
      
      $query = "SELECT b.auto_invite,j.id, j.company_id,j.email,j.phone FROM *PREFIX*hr_jobseeker as j " .
               " LEFT JOIN *PREFIX*hr_jobseeker_token AS t ON t.jobseeker_id=j.id ".($upload ? "" : " AND t.auto_send=0") .
               " LEFT JOIN #__jobs_employer as c ON j.company_id = c.user_id " .
               " LEFT JOIN #__jobs_job as b ON j.job_id = b.id " .
               " LEFT JOIN *PREFIX*hr_company_interview AS ci ON ci.job_id=j.job_id " .
               " WHERE ".($type=='add' ? "t.token IS NULL AND":"")." c.user_id=" . intval($companyid) . " AND j.is_trashed=0 AND b.is_trashed=0 AND j.id IN (" . implode(",", $resumeids) . ") AND b.system=0 " .
               " GROUP BY j.id HAVING COUNT(ci.job_id)>0";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $users = $db->loadAssocList();
      if (!empty($users)) {
         $row = current($users);
         if(empty($row['auto_invite']) && !empty($upload))
            return false;

         $values = array();
         $resumeids = array();
         $sms_setting=PCompanyHelper::getUserQuota($companyid);
         $query = "INSERT IGNORE INTO *PREFIX*hr_jobseeker_token (jobseeker_id,company_id,token,short_token,create_date, auto_send) VALUES ";
         $invite_date = JHtml::_('date', time(), 'Y-m-d H:i:s');
         foreach ($users as $user) {
            if((!empty($user['email']) && !isFakeEmail($user['email'])) || (!empty($user['phone']) && (!empty($sendsms) &&  $sms_setting['sms_left']>0))){
               array_push($resumeids, $user['id']);
               $token = md5(time() . $user['id'] . rand());
               $short_token = shortHash($token,6,'0123456789');
               $values[] = "(" . $db->quote($user['id']) . ", " . $db->quote($user['company_id']) . ", " . $db->quote($token) . ", " . $db->quote($short_token) . ", UTC_TIMESTAMP(), ".($upload ? "1":"0").")";
               // Event Driven Cache/Object Caching - clear any cache related to affected tables 
               CacheHelper::triggerRemove($companyid, array('*PREFIX*hr_jobseeker_token-'.$user['id']));
            }
         }
         if(count($values)<=0){
            $this->setError("简历未正确设置E-mail、公司未设置增值服务或短信包数量不足，邀请失败");
            return false; 
         } 
         $query = $query . implode(",", $values);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute())
            return false;
         
         $data['invite_date'] = $invite_date;
         
         // disable auto send token
         $query = "UPDATE *PREFIX*hr_jobseeker_token AS hjt SET hjt.disable=1 WHERE ".($upload ? "hjt.auto_send=0":"hjt.auto_send=1")." AND hjt.jobseeker_id IN (" . implode(",", $resumeids) . ")";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute())
            return false;

         $query = "SELECT DISTINCT t.jobseeker_id,j.id, j.source,j.name,j.email,j.phone as mobile,j.job_id,b.job_title as jobtitle,b.source as jobsource,b.online_interview_requirements,b.auto_invite,j.company_id,c.comp_name as company, t.token,t.short_token, jus.email as company_email FROM *PREFIX*hr_jobseeker as j " .
                  " LEFT JOIN *PREFIX*hr_jobseeker_token AS t ON t.jobseeker_id=j.id AND ".($upload ? "t.auto_send=1":"t.auto_send=0").
                  " LEFT JOIN #__jobs_employer as c ON j.company_id = c.user_id " .
                  " LEFT JOIN #__users as jus ON jus.id=c.user_id " .
                  " LEFT JOIN #__jobs_job as b ON j.job_id = b.id " .
                  " WHERE t.token IS NOT NULL AND c.user_id=" . intval($companyid) . " AND j.is_trashed=0 AND j.id IN (" . implode(",", $resumeids) . ") AND b.system=0";
         $db->setQuery(PUtil::replaceOCPrefix($db->useLastConnection($query)));
         $users2 = $db->loadAssocList();
         $cuser=PFactory::getUser();
         if (!empty($users2)) {
            foreach ($users2 as $user) {
               $requirements = json_decode($user['online_interview_requirements'], true); 
               if(count($requirements)>1 && empty($cuser->testuser)){  
                  $user['requirement']='笔试评测';
                  $user['url'] = JURI::base().'index.php/apps/interview?token='.$user['token'];
                  $this->sendSMSMessage($user);
               }
               $this->sendEmailMessage($user,$upload);
            }
         }
      }
      
      return $data;
   }
     
   public function getJobseekerInterviewEvent($jobseeker_id=0){
      if(empty($jobseeker_id))
         return null;
      $db = PFactory::getDbo();
      $query = "SELECT v.jobseekername,jj.job_title,d.jobseekerid,d.jobid,d.last_interview_objectid AS objectid,count(o.id) AS offerstat,cr.result,cof.offer_feedback AS offerresult,e.round,t.startdate,t.enddate,cef.entry_feedback AS entryresult,
               group_concat(IF(ee.displayname IS NOT NULL AND length(ee.displayname)>0, ee.displayname, ee.uid)) AS interviewers,
               d.totalinterviewers AS totalinterviewers,
               d.totalfeedbacks AS totalfeedbacks,
               o.objectid AS offereventid,
               e.interview_result,
               d.canceled,
               e.result as resultid,
               e.totalfeedbacks AS last_round_totalfeedbacks
               FROM *PREFIX*calendar_interview_result AS d 
               LEFT JOIN *PREFIX*calendar_objects_details AS e ON e.objectid=d.last_interview_objectid
               LEFT JOIN *PREFIX*calendar_interview_events AS v ON v.objectid=e.objectid 
               LEFT JOIN *PREFIX*calendar_objects AS t ON t.id=v.objectid
               LEFT JOIN *PREFIX*calendar_interview_events AS ee ON ee.parent = t.id
               LEFT JOIN *PREFIX*calendar_offer AS o on o.objectid=d.last_offer_objectid
               LEFT JOIN #__jobs_job AS jj ON d.jobid = jj.id
               LEFT JOIN *PREFIX*calendar_result AS cr ON e.result = cr.id
               LEFT JOIN *PREFIX*calendar_offer_feedback cof ON e.offer_stat = cof.id
               LEFT JOIN *PREFIX*calendar_entry_feedback cef ON e.entry_stat = cef.id
               WHERE d.jobseekerid = ".intval($jobseeker_id) . "
               HAVING jobseekerid IS NOT NULL"; 
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssoc(); 
   }
   
   /**
    * Save resume folder
    * @return boolean
    *
    * @since 3.3
    */
   public function saveFolder() {
      $fid = JRequest::getInt('fid', 0);
      $fname = JRequest::getString('fname', '');
      
      if (empty($fname)) {
         $this->setError('网络异常，请重试！');
         return false;
      }
      
      if (!PCompanyHelper::isJobManager()) {
         $this->setError('没有权限！');
         return false;
      }
      
      // _t<integer> is reserved folder name
      if(preg_match("/^_t\d+$/", $fname)) {
         $this->setError('该文件夹名禁止使用！');
         return false;
      }

      $db = PFactory::getDbo();
      
      $query = "SELECT COUNT(*) FROM *PREFIX*hr_resume_folder WHERE owner=" . $db->quote(OC_User::getUser()) . " AND folder_name=" . $db->quote($fname) . " AND folder_id!=" . intval($fid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if ($db->loadResult()) {
         $this->setError('该文件夹已存在！');
         return false;
      }
      
      if (!$fid) {
         $query = "INSERT IGNORE INTO *PREFIX*hr_resume_folder (folder_name, owner, system, created) VALUES (" . $db->quote($fname) . ", " . $db->quote(OC_User::getUser()) . ", 0, UTC_TIMESTAMP())";
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $this->setError('文件夹创建失败！');
            return false;
         }
      } else {
         $query = "UPDATE *PREFIX*hr_resume_folder SET folder_name=" . $db->quote($fname) . ", created=UTC_TIMESTAMP() WHERE system=0 AND owner=" . $db->quote(OC_User::getUser()) . " AND folder_id=" .intval($fid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $this->setError('文件夹更新失败！');
            return false;
         }
      }
      
      CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_resume_folder'));
   
      return true;
   }
   
   /**
    * Move resumes in folder
    *
    * @return  boolean
    *
    * @since 3.3
    */
   public function saveInFolder() {
      $fid = JRequest::getInt('fid', 0);
      $resumeids = json_decode(JRequest::getString("ids", "[]"));
      
      if(empty($fid) || empty($resumeids)) {
         $this->setError('网络异常，请重试！');
         return false;
      }
      
      if (!PCompanyHelper::isJobManager()) {
         $this->setError('没有权限！');
         return false;
      }
       
      $db = PFactory::getDbo();
      JArrayHelper::toInteger($resumeids);
      
      // get related folderids
      $query = "SELECT DISTINCT hrf.folder_id FROM *PREFIX*hr_resume_folder_files AS hrff " .
               " LEFT JOIN *PREFIX*hr_resume_folder AS hrf ON hrf.folder_id=hrff.folder_id " .
               " WHERE hrf.system=0 AND hrf.owner=" . $db->quote(OC_User::getUser()) . " AND hrff.jobseeker_id IN (" . implode(",", $resumeids) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $relatedFolderids = $db->loadAssocList('', 'folder_id');
      $relatedFolderids = $relatedFolderids ? $relatedFolderids: array();
      
      if (!in_array($fid, $relatedFolderids)) {
         array_push($relatedFolderids, $fid);
      }
      
      // move resumes from other folder
      $query = "UPDATE *PREFIX*hr_resume_folder_files AS hrff " .
               " LEFT JOIN *PREFIX*hr_resume_folder AS hrf ON hrf.folder_id=hrff.folder_id " .
               " SET hrff.folder_id=" .intval($fid) .
               " WHERE hrf.system=0 AND hrf.owner=" . $db->quote(OC_User::getUser()) . " AND hrff.folder_id!=" . intval($fid) . " AND hrff.jobseeker_id IN (" . implode(",", $resumeids) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         $this->setError('移动简历失败！');
         return false;
      }
   
      // add resumes to given folder
      if(!empty($resumeids)) {
         $values = array();
         foreach ($resumeids as $jid) {
            $values[] = "(" . intval($fid) . ", " . $db->quote($jid) . ")";
         }
      
         $query = "INSERT IGNORE INTO *PREFIX*hr_resume_folder_files (folder_id, jobseeker_id) VALUES " . implode(",", $values);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         if (!$db->execute()) {
            $this->setError('移动简历失败！');
            return false;
         }
      }
      
      // update total number of related folder
      $query = "UPDATE *PREFIX*hr_resume_folder AS rf 
                SET rf.num=(SELECT COUNT(*) FROM  *PREFIX*hr_resume_folder_files AS f 
                            LEFT JOIN *PREFIX*hr_jobseeker AS j ON j.id=f.jobseeker_id 
                            LEFT JOIN #__jobs_job AS jj ON jj.id=j.job_id 
                            WHERE f.folder_id=rf.folder_id AND j.is_trashed=0 AND jj.is_trashed=0)
                WHERE rf.folder_id IN (" . implode(",", $relatedFolderids) . ")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      
      CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_resume_folder', '*PREFIX*hr_resume_folder_files'));
       
      return true;
   }
   
   /**
    * Remove resume folder
    * @return boolean
    *
    * @since 3.3
    */
   public function removeFolder() {
      $fid = JRequest::getInt('fid', 0);
   
      if (empty($fid)) {
         $this->setError('网络异常，请重试！');
         return false;
      }
      
      if (!PCompanyHelper::isJobManager()) {
         $this->setError('没有权限！');
         return false;
      }
   
      $db = PFactory::getDbo();
   
      // only allow system=0 deletion 
      $query = "SELECT COUNT(*) FROM *PREFIX*hr_resume_folder WHERE system!=0 AND owner=" . $db->quote(OC_User::getUser()) . " AND folder_id=" . intval($fid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if($db->loadResult() > 0) {
         $this->setError('该文件夹不能删除！');
         return false;
      }

      $query = "DELETE rff.* FROM *PREFIX*hr_resume_folder_files AS rff
                LEFT JOIN *PREFIX*hr_resume_folder rf ON rf.folder_id=rff.folder_id WHERE rf.owner=" . $db->quote(OC_User::getUser()) . " AND rff.folder_id=" . intval($fid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute())
         return false;
      
      $query = "DELETE FROM *PREFIX*hr_resume_folder WHERE owner=" . $db->quote(OC_User::getUser()) . " AND folder_id=" . intval($fid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute())
         return false;
      
      CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_resume_folder'));
       
      return true;
   }

   public function checkResumeExists($email="", $job_id=0, $source=null, $jobseekerid=0){
      if(empty($email) || empty($job_id))
         return false; 

      $user=PFactory::getMasterUser();
      $db=PFactory::getDbo();    
      $query = 'SELECT COUNT(*) ' .
               ' FROM *PREFIX*hr_jobseeker WHERE email= '.$db->quote($email).' AND job_id='.intval($job_id).' AND company_id ='.intval($user->id).' AND id !='.intval($jobseekerid);

      if($source)
         $query .= " AND source=" . $db->quote($source); 

      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadResult() > 0 ? true : false; 
   }

   /**
    * Set resume job
    * @return boolean
    *
    * @since 3.3
    */
   public function changeJobPosition() {
      $jobid = JRequest::getInt('jobid', 0);
      $jobseekerid = JRequest::getInt('jobseekerid', 0);

      if (empty($jobid) || empty($jobseekerid)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      $jobseekerinfo=$this->getResumeJobDetail($jobseekerid);

      if (!PResumesHelper::checkResumeUploadPermission($jobseekerinfo, $jobid)) {
         $this->setError('没有权限！');
         return false;
      }

      if($this->checkResumeExists($jobseekerinfo->email,$jobid,null, $jobseekerid)){
         $this->setError('简历已存在！');
         return false;
      }
      $db = PFactory::getDbo();
      $query = "UPDATE *PREFIX*hr_jobseeker SET job_id=" . intval($jobid) . " WHERE id=" . intval($jobseekerid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      
      //update jobseeker's persontest result
      PInterviewHelper::updatePersontestResult($jobseekerid);

      $user = PFactory::getMasterUser();
      $jobids=array();
      $tables = array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker-'.$jobseekerid);

      if($jobseekerinfo->job_id!=$jobid){
         if($jobseekerinfo->job_id)
            array_push($jobids, $jobseekerinfo->job_id);
         if($jobid)
            array_push($jobids, $jobid);
         $query="UPDATE *PREFIX*calendar_interview_events SET jobid=".intval($jobid)." WHERE jobseekerid=".intval($jobseekerid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
         $query="UPDATE *PREFIX*calendar_interview_result SET jobid=".intval($jobid)." WHERE jobseekerid=".intval($jobseekerid);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
         array_push($tables, '*PREFIX*calendar_interview_events', '*PREFIX*calendar_interview_result');
      }
      if(!empty($jobids)){
         $query="UPDATE #__jobs_job AS a
                  SET num_applied = (SELECT COUNT(*) FROM  *PREFIX*hr_jobseeker WHERE is_trashed=0 AND job_id=a.id)
                  WHERE a.id in (".implode(',', $jobids).') AND a.employer_id='.$user->id;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();

         array_push($tables, '#__jobs_job');
         foreach ($jobids as $tmp_job) {
            array_push($tables, '#__jobs_job-'.intval($tmp_job));
         }
      }

      CacheHelper::triggerRemove($user->id, $tables);
      if (!PFactory::isMasterUser())
         CacheHelper::triggerRemove(OC_User::getUser(), array('*PREFIX*hr_jobseeker'));

      return true;
   }

   public function addResumeComments($jobseeker_id, $commenttext, $companyid, $type) {
      $db = PFactory::getDbo();
      //check if jobseeker exists and belong the company
      $query = " SELECT * FROM  *PREFIX*hr_jobseeker AS j" . 
               " WHERE j.id = " . intval($jobseeker_id) . " AND j.company_id = " . $db->quote($companyid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadAssoc();
      if(empty($result)) { return false; }

      $query="INSERT INTO *PREFIX*hr_jobseeker_comments ".
             "VALUES(NULL," . intval($jobseeker_id) . ",'',".$db->quote($commenttext).",UTC_TIMESTAMP()," . $db->quote(OC_User::getUser()). ",".intval($type).")";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      CacheHelper::triggerRemove($jobseeker_id, array("*PREFIX*hr_jobseeker_comments-".$jobseeker_id));
      $result = $db->insertid();
      PResumesHelper::updateProcessed($jobseeker_id);
      return $result;
   }

   public function getResumeComments($companyid, $jobseeker_id, $limit=5) {
      if (!$jobseeker_id) { return array(); }
      if ($limit < 0) { $limit = 5; }

      $db = PFactory::getDbo();
      $query = " SELECT SQL_CALC_FOUND_ROWS c.* , IFNULL(j.name,o.displayname) AS name " .
               " FROM *PREFIX*hr_jobseeker_comments AS c " .
               " LEFT JOIN #__users AS j ON j.id=c.userid_commenter " .
               " LEFT JOIN *PREFIX*users AS o ON o.uid=c.userid_commenter " .
               " INNER JOIN *PREFIX*hr_jobseeker AS js ON js.id = c.jobseeker_id" .
               " WHERE c.jobseeker_id = " . intval($jobseeker_id) . " AND js.company_id = " . $db->quote($companyid) .
               " ORDER BY id DESC ";
      $cache = CacheHelper::getCache('app_resumes');
      $data = $cache->call(array($this, '_getResumeComments'), $query, $limit);
      CacheHelper::setTriggers($jobseeker_id, $cache, array("*PREFIX*hr_jobseeker_comments-".$jobseeker_id));
      return $data;
   }

   public function _getResumeComments($query, $limit) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), 0, $limit);
      $rows = $db->loadAssocList();
      $db->setQuery($db->useLastConnection('SELECT FOUND_ROWS();'));
      $numtotal = $db->loadResult();
      $left = $numtotal - $limit <= 0 ? 0 : ($numtotal - $limit);
      foreach ($rows as &$row) {
         $row["commented"] = JHtml::date($row['commented'], 'Y-m-d H:i:s');
         $row["isself"] = (OC_User::getUser() == $row["userid_commenter"] ? true : false );
      }
      return array('list' => $rows, 'total' => $numtotal, 'left' => $left);
   }

   public function deleteResumeComments($comment_id, $jobseeker_id, $companyid) {
      $db = PFactory::getDbo();
      //check if jobseeker exists and belong the company
      $query = " SELECT * FROM  *PREFIX*hr_jobseeker AS j" . 
               " WHERE j.id = " . intval($jobseeker_id) . " AND j.company_id = " . $db->quote($companyid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadAssoc();
      if(empty($result)) { return false; }

      $query="DELETE FROM *PREFIX*hr_jobseeker_comments WHERE id = " . intval($comment_id) . (PFactory::isMasterUser() ? '' : " AND userid_commenter = " . $db->quote(OC_User::getUser()));
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $db->execute();
      $num_row = $db->getAffectedRows();
      if($num_row <= 0) { return false; }
      CacheHelper::triggerRemove($jobseeker_id, array("*PREFIX*hr_jobseeker_comments-".$jobseeker_id));
      return true;
   }
}
